package cad;
import java.sql.*;



public class Connexion {
    private static Connection conn; 
    
    public Connexion() throws Exception{
        
        Class.forName("org.h2.Driver");
        try {
            conn = DriverManager.getConnection("jdbc:h2:sfdb;IFEXISTS=TRUE", "sa", "");
        } catch (SQLException e) {
            System.out.println("ERREUR DE CONNEXION A LA BASE DE DONNEE");
        }
    }
    
    public String[] nomSimulations(){
        String query = "SELECT NOM FROM SIMULATION";
        String[] resultats = new String[5000];
        try {
            Statement statement = conn.createStatement();
            try {
                ResultSet resultSet = statement.executeQuery(query);
                int i = 0;
                while (resultSet.next()) {
                    //traiter resultats
                    resultats[i] = resultSet.getString("NOM");
                    i++;
                }
            } catch (SQLException e) {
                System.out.println("Erreur de requête : "+e);
            } finally {
                if (statement != null) { statement.close(); }
            }
        } catch (SQLException e) {
            System.out.println("Erreur de connexion");
        }
        return resultats;
    }
    
    public int[] chargerSimulationTailleMode(String nomSimu){
        String query = "SELECT TAILLE,MODE FROM SIMULATION WHERE NOM='"+nomSimu+"';";
        int[] resultats = new int[2];
        try {
            Statement statement = conn.createStatement();
            try {
                ResultSet resultSet = statement.executeQuery(query);
                while (resultSet.next()) {
                    resultats[0] = resultSet.getInt("TAILLE");
                    resultats[1] = resultSet.getInt("MODE");
                }
            } catch (SQLException e) {
                System.out.println("Erreur de requête : "+e);
            } finally {
                if (statement != null) { statement.close(); }
            }
        } catch (SQLException e) {
            System.out.println("Erreur de connexion");
        }
        return resultats;
    }
    
    public int[][] chargerGrille(String nomSimu, int taille){
        int[][] grille = new int[taille][taille];
        
        String query = "SELECT * FROM CASES C JOIN SIMULATION S ON C.ID=S.ID WHERE S.NOM='"+nomSimu+"';";
        try {
            Statement statement = conn.createStatement();
            try {
                ResultSet resultSet = statement.executeQuery(query);
                while (resultSet.next()) {
                    int x = resultSet.getInt("X");
                    int y = resultSet.getInt("Y");
                    int type = resultSet.getInt("TYPE");
                    grille[x][y] = type;
                }
            } catch (SQLException e) {
                System.out.println("Erreur de requête : "+e);
            } finally {
                if (statement != null) { statement.close(); }
            }
        } catch (SQLException e) {
            System.out.println("Erreur de connexion");
        }
        
        return grille;
    }
    
    public void sauvegarderGrille(String query){
        try {
            Statement statement = conn.createStatement();
            statement.executeUpdate(query);

        } catch (SQLException exc) {
            System.out.println("Erreur d'insertion : "+exc);
        }
    }
    
    private void effacerGrille(String nom){
                //Recupération de l'id de la nouvelle Simulation
        String query = "SELECT ID FROM SIMULATION WHERE NOM='"+nom+"'";
        int id = 0;
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next()){
                id = rs.getInt("ID");
            }
        } catch (SQLException exc) {
            System.out.println("Erreur d'insertion : "+exc);
        }
        
        query = "DELETE FROM CASES WHERE ID="+id+";";
        try {
            Statement statement = conn.createStatement();
            statement.executeUpdate(query);

        } catch (SQLException exc) {
            System.out.println("Erreur de suppression : "+exc);
        }
    }
    
    public int sauvegarderSimulation(String nom, int mode, int taille){
        //Destruction de la Simulation du même nom existante
        String query = "DELETE FROM SIMULATION WHERE NOM='"+nom+"';";
        try {
            Statement statement = conn.createStatement();
            statement.executeUpdate(query);

        } catch (SQLException exc) {
            System.out.println("Erreur de suppression : "+exc);
        }
        
        //Destruction de la grille correspondant à cette simulation
        effacerGrille(nom);
        
        //Insertion de la nouvelle simulation dans la bdd
        query = "INSERT INTO SIMULATION (NOM, MODE, TAILLE) "
                + "VALUES ('"+nom+"', "+mode+", "+taille+");";
        try {
            Statement statement = conn.createStatement();
            statement.executeUpdate(query);

        } catch (SQLException exc) {
            System.out.println("Erreur d'insertion : "+exc);
        }
        
        //Recupération de l'id de la nouvelle Simulation
        query = "SELECT ID FROM SIMULATION WHERE NOM='"+nom+"'";
        int id = 0;
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next()){
                id = rs.getInt("ID");
            }
        } catch (SQLException exc) {
            System.out.println("Erreur d'insertion : "+exc);
        }
        return id;
    }
}